﻿using Face8280WebSocketServer.Model.People;
using Face8280WebSocketServer.Utility.EntityFrameworkCore;
using Face8280WebSocketServer.Utility.Model;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using Microsoft.Extensions.FileProviders;
using System.Net.Http;
using Microsoft.AspNetCore.Http;

namespace Face8280WebSocketServer.Controllers
{
    [Route("[controller]")]
    [ApiController]
    public class SQLiteTestController : ControllerBase
    {
        private readonly ILogger<SQLiteTestController> _logger;

        public SQLiteTestController(ILogger<SQLiteTestController> logger)
        {
            _logger = logger;

        }


        [Route("TestInsert")]
        [HttpGet]
        public JsonResult TestInsert()
        {
            _logger.LogInformation($"开始测试SQLite插入");

            var Services = this.HttpContext.RequestServices;
            var dbconn = Services.GetService<DbContext>();
            var db = dbconn.Set<FacePeopleTest>();
            CheckTestTable(dbconn.Database);
            Stopwatch wch = new Stopwatch();


            wch.Start();
            _logger.LogInformation($"开始清空所有人员");
            //清空所有人

            db.FromSqlRaw("Delete from FacePeopleTest");
            dbconn.SaveChanges();

            using (var trans = dbconn.Database.BeginTransaction())
            {
                dbconn.Database.ExecuteSqlRaw("Delete from FacePeopleTest");
                trans.Commit();
            }

            wch.Stop();
            _logger.LogInformation($"清空所有人员完毕,{wch.ElapsedMilliseconds}ms");

            //生产测试数据

            List<FacePeopleTest> facePeoples = new List<FacePeopleTest>();
            var iCreateCount = 100_0000;

            wch.Restart();
            _logger.LogInformation($"生产测试数据 {iCreateCount}");

            for (int i = 0; i < iCreateCount; i++)
            {
                FacePeopleTest people = new FacePeopleTest();
                people.EmployeeID = i;
                people.EmployeeName = $"人员_{i}";
                people.EmployeeJob = $"职务_{i}";
                people.EmployeePassword = $"12345";
                people.EmployeePhotoWay = "path";
                people.EmployeePhoto = $"https://oss2.pc15.net/fc8280/peopletest/{i}.jpg";
                people.EmployeeIC = i;
                people.EmployeePhone = $"手机号：{i}";
                people.EmployeeIdentity = $"身份证信息：{i}";
                people.EmployeeAddress = $"地址信息：{i}";
                people.PhotoLen = i;

                facePeoples.Add(people);
            }
            wch.Stop();
            _logger.LogInformation($"生产测试数据完毕,{wch.ElapsedMilliseconds}ms");


            //导入到EF
            wch.Restart();
            _logger.LogInformation($"开始导入到EF");

            foreach (var item in facePeoples)
            {
                db.Add(item);
            }
            wch.Stop();
            _logger.LogInformation($"导入到EF完毕,{wch.ElapsedMilliseconds}ms");


            //开始提交到数据库
            wch.Restart();
            _logger.LogInformation($"开始提交到数据库");


            dbconn.SaveChanges();

            _logger.LogInformation($"提交到数据库完毕,{wch.ElapsedMilliseconds}ms");

            return new JsonResult(new JsonResultModel());
        }



        [Route("TestInsertBySQL")]
        [HttpGet]
        public JsonResult TestInsertBySQL()
        {
            _logger.LogInformation($"开始测试SQLite插入");

            var Services = this.HttpContext.RequestServices;
            var dbconn = Services.GetService<DbContext>();
            var db = dbconn.Database;
            CheckTestTable(db);
            Stopwatch wch = new Stopwatch();


            wch.Start();
            _logger.LogInformation($"开始清空所有人员");
            //清空所有人

            dbconn.SaveChanges();

            using (var trans = db.BeginTransaction())
            {
                db.ExecuteSqlRaw("Delete from FacePeopleTest");
                trans.Commit();
            }

            wch.Stop();
            _logger.LogInformation($"清空所有人员完毕,{wch.ElapsedMilliseconds}ms");

            //生产测试数据

            List<FacePeopleTest> facePeoples = new List<FacePeopleTest>();
            var iCreateCount = 100_0000;

            wch.Restart();
            _logger.LogInformation($"生产测试数据 {iCreateCount}");

            for (int i = 0; i < iCreateCount; i++)
            {
                FacePeopleTest people = new FacePeopleTest();
                people.EmployeeID = i;
                people.EmployeeName = $"人员_{i}";
                people.EmployeeJob = $"职务_{i}";
                people.EmployeePassword = $"12345";
                people.EmployeePhotoWay = "path";
                people.EmployeePhoto = $"https://oss2.pc15.net/fc8280/peopletest/{i}.jpg";
                people.EmployeeIC = i;
                people.EmployeePhone = $"手机号：{i}";
                people.EmployeeIdentity = $"身份证信息：{i}";
                people.EmployeeAddress = $"地址信息：{i}";
                people.PhotoLen = i;

                facePeoples.Add(people);
            }
            wch.Stop();
            _logger.LogInformation($"生产测试数据完毕,{wch.ElapsedMilliseconds}ms");


            //开始提交到数据库
            wch.Restart();
            _logger.LogInformation($"开始提交到数据库");


            using (var trans = db.BeginTransaction())
            {
                foreach (var people in facePeoples)
                {
                    string sSQL = @"INSERT INTO [FacePeopleTest] (CreateTime, 
EmployeeAddress, EmployeeIC, EmployeeID, EmployeeIdentity, 
EmployeeJob, EmployeeName, EmployeePassword, EmployeePhone, EmployeePhoto, EmployeePhotoWay, EmployeeShold, LastUpdatetime, PhotoLen)
VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}); ";
                    db.ExecuteSqlRaw(sSQL,
                        people.CreateTime,
                        people.EmployeeAddress,
                        people.EmployeeIC,
                        people.EmployeeID,
                        people.EmployeeIdentity,
                        people.EmployeeJob,
                        people.EmployeeName,
                        people.EmployeePassword,
                        people.EmployeePhone,
                        people.EmployeePhoto,
                        people.EmployeePhotoWay,
                        people.EmployeeShold,
                        people.LastUpdatetime,
                        people.PhotoLen);
                }
                trans.Commit();
            }

            wch.Stop();
            _logger.LogInformation($"提交到数据库完毕,{wch.ElapsedMilliseconds}ms");

            return new JsonResult(new JsonResultModel());
        }

        private void CheckTestTable(DatabaseFacade db)
        {
            var sCreateTable = @"
CREATE TABLE FacePeopleTest (
    ID               INTEGER  NOT NULL
                              CONSTRAINT PK_FacePeopleTest PRIMARY KEY AUTOINCREMENT,
    EmployeeId       BIGINT   NOT NULL,
    EmployeeName     TEXT,
    EmployeeJob      TEXT,
    EmployeePassword TEXT,
    EmployeePhotoWay TEXT,
    EmployeePhoto    TEXT,
    EmployeeIc       BIGINT,
    EmployeePhone    TEXT,
    EmployeeShold    REAL     NOT NULL,
    EmployeeIdentity TEXT,
    EmployeeAddress  TEXT,
    PhotoLen         INTEGER,
    LastUpdatetime   DATETIME,
    CreateTime       DATETIME
);
";
            var sCheckTableSQL = @"
    SELECT count(*) FROM sqlite_master
    WHERE type='table' and name = 'FacePeopleTest'
    ORDER BY name;
";
            var iCount = (long)db.ExecuteScalar(sCheckTableSQL);
            if (iCount == 0)
            {

                using (var trans = db.BeginTransaction())
                {
                    db.ExecuteSqlRaw(sCreateTable);
                    trans.Commit();
                }
            }

        }

        [Route("TestInsertBySQLBatch")]
        [HttpGet]
        public JsonResult TestInsertBySQLBatch()
        {
            _logger.LogInformation($"开始测试SQLite插入");

            var Services = this.HttpContext.RequestServices;
            var dbconn = Services.GetService<DbContext>();
            var db = dbconn.Database;
            CheckTestTable(db);
            Stopwatch wch = new Stopwatch();


            wch.Start();
            _logger.LogInformation($"开始清空所有人员");
            //清空所有人

            dbconn.SaveChanges();

            using (var trans = db.BeginTransaction())
            {
                db.ExecuteSqlRaw("Delete from FacePeopleTest");
                trans.Commit();
            }

            wch.Stop();
            _logger.LogInformation($"清空所有人员完毕,{wch.ElapsedMilliseconds}ms");

            //生产测试数据

            List<FacePeopleTest> facePeoples = new List<FacePeopleTest>();
            var iCreateCount = 100_00;

            wch.Restart();
            _logger.LogInformation($"生产测试数据 {iCreateCount}");

            for (int i = 0; i < iCreateCount; i++)
            {
                FacePeopleTest people = new FacePeopleTest();
                people.EmployeeID = i;
                people.EmployeeName = $"人员_{i}";
                people.EmployeeJob = $"职务_{i}";
                people.EmployeePassword = $"12345";
                people.EmployeePhotoWay = "path";
                people.EmployeePhoto = $"https://oss2.pc15.net/fc8280/peopletest/{i}.jpg";
                people.EmployeeIC = i;
                people.EmployeePhone = $"手机号：{i}";
                people.EmployeeIdentity = $"身份证信息：{i}";
                people.EmployeeAddress = $"地址信息：{i}";
                people.PhotoLen = i;

                facePeoples.Add(people);
            }
            wch.Stop();
            _logger.LogInformation($"生产测试数据完毕,{wch.ElapsedMilliseconds}ms");

            //开始提交到数据库
            wch.Restart();


            //开始提交到数据库
            wch.Restart();
            _logger.LogInformation($"开始拼装SQL语句");

            StringBuilder buf = new StringBuilder();
            buf.Append(@"INSERT INTO [FacePeopleTest] (CreateTime, 
EmployeeAddress, EmployeeIC, EmployeeID, EmployeeIdentity, 
EmployeeJob, EmployeeName, EmployeePassword, EmployeePhone, EmployeePhoto, EmployeePhotoWay, EmployeeShold, LastUpdatetime, PhotoLen)
VALUES ");
            foreach (var people in facePeoples)
            {
                buf.Append("('");
                buf.Append(people.CreateTime.ToString("yyyy-MM-dd HH:mm:ss.ffff")).Append("','");
                buf.Append(people.EmployeeAddress).Append("',");
                buf.Append(people.EmployeeIC).Append(",");
                buf.Append(people.EmployeeID).Append(",'");
                buf.Append(people.EmployeeIdentity).Append("','");
                buf.Append(people.EmployeeJob).Append("','");
                buf.Append(people.EmployeeName).Append("','");
                buf.Append(people.EmployeePassword).Append("','");
                buf.Append(people.EmployeePhone).Append("','");
                buf.Append(people.EmployeePhoto).Append("','");
                buf.Append(people.EmployeePhotoWay).Append("',");
                buf.Append(people.EmployeeShold).Append(",'");
                buf.Append(people.LastUpdatetime.ToString("yyyy-MM-dd HH:mm:ss.ffff")).Append("',");
                buf.Append(people.PhotoLen).Append("),");
            }
            buf.Length -= 1;


            wch.Stop();
            _logger.LogInformation($"拼装SQL语句完毕, 语句长度：{buf.Length},{wch.ElapsedMilliseconds}ms");
            wch.Restart();
            _logger.LogInformation($"开始提交到数据库");
            using (var trans = db.BeginTransaction())
            {


                db.ExecuteSqlRaw(buf.ToString());

                trans.Commit();
            }

            wch.Stop();
            _logger.LogInformation($"提交到数据库完毕,{wch.ElapsedMilliseconds}ms");

            return new JsonResult(new JsonResultModel());
        }


        [Route("TestUpdateBySQLBatch")]
        [HttpGet]
        public JsonResult TestUpdateBySQLBatch()
        {
            _logger.LogInformation($"开始测试SQLite更新");

            var Services = this.HttpContext.RequestServices;
            var dbconn = Services.GetService<DbContext>();
            var db = dbconn.Database;
            CheckTestTable(db);
            Stopwatch wch = new Stopwatch();




            //生产测试数据

            List<FacePeopleTest> facePeoples = new List<FacePeopleTest>();
            var iCreateCount = 100;

            wch.Restart();
            _logger.LogInformation($"生产测试数据 {iCreateCount}");

            for (int i = 0; i < iCreateCount; i++)
            {
                FacePeopleTest people = new FacePeopleTest();
                people.ID = i + 1;
                people.EmployeeID = i;
                people.EmployeeName = $"人员_{i}";
                people.EmployeeJob = $"职务_{i}";
                people.EmployeePassword = "12345";
                people.EmployeePhotoWay = "path";
                people.EmployeePhoto = $"https://oss2.pc15.net/fc8280/peopletest/{i}.jpg";
                people.EmployeeIC = i;
                people.EmployeePhone = $"手机号：{i}";
                people.EmployeeIdentity = $"身份证信息：{i}";
                people.EmployeeAddress = $"地址信息：{i}";
                people.PhotoLen = i;

                facePeoples.Add(people);
            }
            wch.Stop();
            _logger.LogInformation($"生产测试数据完毕,{wch.ElapsedMilliseconds}ms");

            //开始提交到数据库
            wch.Restart();


            //开始提交到数据库
            wch.Restart();
            _logger.LogInformation($"开始拼装SQL语句");

            //using (var trans = db.BeginTransaction())
            //{


            StringBuilder buf = new StringBuilder();

            foreach (var people in facePeoples)
            {
                //buf.Clear();
                buf.Append("Update  [FacePeopleTest] Set ");

                buf.Append("CreateTime = '");
                buf.Append(people.CreateTime.ToString("yyyy-MM-dd HH:mm:ss.ffff")).Append("',");

                buf.Append("EmployeeAddress = '");
                buf.Append(people.EmployeeAddress).Append("',");

                buf.Append("EmployeeIC = ");
                buf.Append(people.EmployeeIC).Append(",");

                buf.Append("EmployeeID = ");
                buf.Append(people.EmployeeID).Append(",");

                buf.Append("EmployeeIdentity = '");
                buf.Append(people.EmployeeIdentity).Append("',");

                buf.Append("EmployeeJob = '");
                buf.Append(people.EmployeeJob).Append("',");

                buf.Append("EmployeeName = '");
                buf.Append(people.EmployeeName).Append("',");

                buf.Append("EmployeePassword = '");
                buf.Append(people.EmployeePassword).Append("',");

                buf.Append("EmployeePhone = '");
                buf.Append(people.EmployeePhone).Append("',");

                buf.Append("EmployeePhoto = '");
                buf.Append(people.EmployeePhoto).Append("',");

                buf.Append("EmployeePhotoWay = '");
                buf.Append(people.EmployeePhotoWay).Append("',");

                buf.Append("EmployeeShold = ");
                buf.Append(people.EmployeeShold).Append(",");

                buf.Append("LastUpdatetime = '");
                buf.Append(people.LastUpdatetime.ToString("yyyy-MM-dd HH:mm:ss.ffff")).Append("',");

                buf.Append("PhotoLen = ");
                buf.Append(people.PhotoLen);

                buf.Append(" Where ID = ");
                buf.Append(people.ID).Append(";");

                //db.ExecuteSqlRaw(buf.ToString());

            }
            //trans.Commit();
            //}


            wch.Stop();
            _logger.LogInformation($"拼装SQL语句完毕, 语句长度：{buf.Length},{wch.ElapsedMilliseconds}ms");
            wch.Restart();
            _logger.LogInformation($"开始提交到数据库");
            using (var trans = db.BeginTransaction())
            {


                db.ExecuteSqlRaw(buf.ToString());

                trans.Commit();
            }

            wch.Stop();
            _logger.LogInformation($"提交到数据库完毕,{wch.ElapsedMilliseconds}ms");

            return new JsonResult(new JsonResultModel());
        }

        [Route("TestReadID")]
        [HttpGet]
        public async Task<JsonResult> TestReadID()
        {

            var Services = this.HttpContext.RequestServices;
            var dbconn = Services.GetService<DbContext>();
            var db = dbconn.Set<FacePeopleTest>();
            var qst = from p in db.AsNoTracking()
                      select new
                      {
                          p.EmployeeID,
                          p.ID
                      };
            var dit = await qst.ToDictionaryAsync(x => x.ID);
            return new JsonResult(new JsonResultModel(dit.Count));
        }



        [Route("CreatePeopleExcel/{count}")]
        [HttpGet]
        public async Task<JsonResult> CreatePeopleExcel(int count)
        {
            if (count < 0 || count > 100_0000)
            {
                return new JsonResult(new JsonResultModel(1, "值范围错误"));
            }
            var sXLSFileName = $"input_{DateTime.Now:yyyyMMdd_HHmmss_ffff}.xlsx";

            var sPath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "Excel");
            if (!Directory.Exists(sPath))
            {
                Directory.CreateDirectory(sPath);
            }
            var sXLSFile = Path.Combine(sPath, sXLSFileName);
            //System.IO.MemoryStream xlsStream=new MemoryStream();
            await Task.Run(() =>
            {
                IWorkbook book = new XSSFWorkbook();

                var sheet = book.CreateSheet("人员表");

                //创建标题行
                var sColTitle = "人员编号,人员姓名,密码,卡号,职务,联系方式,身份证信息,地址信息,照片URL,人脸识别阈值".Split(",");
                var iCols = sColTitle.Length;
                var row = sheet.CreateRow(0);
                for (int i = 0; i < iCols; i++)
                {
                    row.CreateCell(i).SetCellValue(sColTitle[i]);
                    sheet.SetColumnWidth(i, 4500);
                    if (i == 8)
                    {
                        sheet.SetColumnWidth(i, 12000);
                    }
                }


                //创建内容行
                for (int i = 0; i < count; i++)
                {
                    row = sheet.CreateRow(i + 1);

                    row.CreateCell(0).SetCellValue(i);//人员编号
                    row.CreateCell(1).SetCellValue($"人员{i}");//人员姓名
                    row.CreateCell(2).SetCellValue($"{1000 + i}");//密码
                    row.CreateCell(3).SetCellValue($"{1000 + i}");//卡号
                    row.CreateCell(4).SetCellValue($"职务{i}");//职务
                    row.CreateCell(5).SetCellValue($"联系方式{i}");//联系方式
                    row.CreateCell(6).SetCellValue($"身份证信息{i}");//身份证信息
                    row.CreateCell(7).SetCellValue($"地址信息{i}");//地址信息
                    row.CreateCell(8).SetCellValue($"https://oss2.pc15.net/FC8280T/Test/{i}.jpg");//照片URL
                    row.CreateCell(9).SetCellValue(0);//人脸识别阈值
                }


                using (var FileStreamfile = new FileStream(sXLSFile, FileMode.Create))
                {
                    book.Write(FileStreamfile);
                };
                book.Close();
            });

            return new JsonResult(new JsonResultModel($"/Excel/{sXLSFileName}"));

        }

        [Route("TestReadURL")]
        [HttpPost]
        public async Task<JsonResult> TestReadURL([FromForm]string URL)
        {
            IHttpClientFactory clientFactory = this.HttpContext.RequestServices.GetService(typeof(IHttpClientFactory)) as IHttpClientFactory;
            HttpClient httpClient = clientFactory.CreateClient();
            Stopwatch wch = new Stopwatch();
            wch.Start();

            var request = new HttpRequestMessage(HttpMethod.Head, URL);
            var response = await httpClient.SendAsync(request);
            

            wch.Stop();

            return new JsonResult(new JsonResultModel(1,$"耗时：{wch.ElapsedMilliseconds} ms") { content = response });
        }


        [Route("GCMemory")]
        [HttpGet]
        public JsonResult GCMemory()
        {
            GC.Collect();

            return new JsonResult(GC.GetGCMemoryInfo());
        }


        [Route("TestBody")]
        [HttpPost]
        public JsonResult TestBody()
        {
            var ret = this.HttpContext.Request;
            string json=string.Empty;
            try
            {
                ret.EnableBuffering();
                ret.Body.Position = 0;
                using (var requestReader = new StreamReader(ret.Body, encoding: System.Text.Encoding.UTF8, leaveOpen: true))
                {
                    json = requestReader.ReadToEndAsync().Result;
                   
                }
                ret.Body.Position = 0;
            }
            catch (Exception ex)
            {
                _logger.LogError("读取Body时发生错误！" + ex.Message);

            }

            


            return new JsonResult(new JsonResultModel(json));
        }

    }
}
